﻿// --------------------------------------------------------------------------------------------------------------------
// <copyright file="LinkSqLiteDB.cs" company="HelloWorld">
//   BY田新宇
// </copyright>
// <summary>
// 成绩部分连接和操作数据库的方法
// </summary>
// --------------------------------------------------------------------------------------------------------------------

namespace WindowsFormsApp2.Service
{
    using System;
    using System.Data;
    using System.Data.SQLite;
    using System.Windows.Forms;

    using WindowsFormsApp2.Model;

    // 创建数据库连接的工具类

    /// <summary>
    /// The link SQLite database.
    /// </summary>
    internal class LinkSqLiteDB
    {
        /// <summary>
        /// The SQLite connection.
        /// </summary>
        private SQLiteConnection sqlCnn; // 创建数据库连接对象

        /// <summary>
        /// The SQLite command.
        /// </summary>
        private SQLiteCommand sqlCmd; // 创建执行sql的对象

        /// <summary>
        /// The database tag.
        /// </summary>
        private bool tag;

        // 创建数据库连接

        /// <summary>
        /// The connection.
        /// </summary>
        /// <returns>
        /// The <see cref="bool"/>.
        /// </returns>
        public bool DBConn()
        {
            this.sqlCnn = new SQLiteConnection();
            this.sqlCmd = new SQLiteCommand();

            // sqlCnn.ConnectionString = "server = 'localhost'; uid = 'root'; pwd = 'tian951028'; database = 'grade';";//连接字符串
            this.sqlCnn.ConnectionString = "Data Source=../../../../mfc.db";
            this.sqlCmd.Connection = this.sqlCnn;
            try
            {
                this.sqlCnn.Open();
                this.tag = true;
                if (this.tag)
                {
                    // Console.WriteLine("数据库连接成功");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, @"错误！");
            }

            return this.tag;
        }

        // 执行增删改的sql

        /// <summary>
        /// The update delete add.
        /// </summary>
        /// <param name="str">
        /// The string.
        /// </param>
        /// <returns>
        /// The <see cref="bool"/>.
        /// </returns>
        public bool UpdateDeleteAdd(string str)
        {
            this.sqlCmd.CommandText = str;
            return this.sqlCmd.ExecuteNonQuery() > 0;
        }

        // 关闭数据库连接

        /// <summary>
        /// The database close.
        /// </summary>
        public void DBClose()
        {
            this.sqlCnn.Close();
            this.sqlCnn.Dispose();

            // Console.WriteLine("数据库连接关闭");
            // Console.ReadKey();
        }

        // 获取登录时所需要的数据login_info

        /// <summary>
        /// The get login data.
        /// </summary>
        /// <param name="str">
        /// The string.
        /// </param>
        /// <returns>
        /// The <see cref="bool"/>.
        /// </returns>
        public bool GetLoginData(string str)
        {
            this.sqlCmd.CommandText = str;
            var rec = this.sqlCmd.ExecuteReader(); // 创建结果集
            while (rec.Read())
            {
                // 查询到数据结果
                LoginInfo.Stuid = rec[0].ToString();  // 将用户名存放到数据实体类
                LoginInfo.Password = rec.GetString(1); // 将密码存放在数据实体类
                LoginInfo.Stuname = rec.GetString(2); // 姓名
                Console.WriteLine(@"查询到的信息：学号{0}，密码{1}，姓名{2}", LoginInfo.Stuid, LoginInfo.Password, LoginInfo.Stuname);
                rec.Close();
                return true;    // 查询到用户数据
            }

            return false;
        }

        /// <summary>
        /// The get score ds.
        /// </summary>
        /// <param name="sql">
        /// The SQLite.
        /// </param>
        /// <returns>
        /// The <see cref="DataSet"/>.
        /// </returns>
        public DataSet GetScoreDs(string sql)
        {
            var ds = new DataSet();

            // SQLiteConnection mySqlConnection = new SQLiteConnection("Data Source=" + Application.ExecutablePath.Substring(0, Application.ExecutablePath.IndexOf(@"gradeManager\")) + "mfc.db");
            var mySqlConnection = this.sqlCnn;
            var mySqlCommand = new SQLiteCommand(sql, mySqlConnection);
            var dataAdapter = new SQLiteDataAdapter(mySqlCommand);
            dataAdapter.Fill(ds);
            return ds;
        }
    }
}
